ALTER function fn_all_saleshold_orders_display(@isSample bit)
returns  @fn_result Table(SalesOrder_ID int,
		SO_Customer_PO_Number varchar(50),
		Customer_Id int,
		LineItem_Id int,
		COOL_Line_Item_No int,
		Product_ID int,
 		COOL_Description varchar(500),
		COOL_Qty int,
		COOL_Unit_Cost float,
		COOL_Amount float,
		COOL_Requested_Date datetime,
		COOL_Sugg_Schedule_Date_With_Substitutables datetime,
		COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
		COOL_Booking_Date datetime,
		COOL_Schedule_Date datetime,
		COOL_Revised_Date datetime,
		COOL_Status varchar(50),
		COOL_Taxable int,
		COOL_Is_Cancel int,
		SalesOrder_No varchar(50),
		Final_Cool_Status varchar(50),
		Customer_PartNumber varchar(50), 
		SO_Booking_Date datetime, 
		SO_ODM_ID int,
		SHIPPING_TERMS VARCHAR(50))
as
begin
	declare @sales_order_id int
	declare @sales_customerpo varchar(50)
      	declare @customerid int
	declare @line_item_id int
        declare @cool_line_item_no int
	declare @temp_product_id int
	declare @temp_cool_description varchar(500)
	declare @temp_cool_qty int
	declare @temp_cool_unit_cost float
	declare @temp_cool_amount float
        declare @temp_cool_requested_date datetime
	declare @temp_cool_sugg_date_with_substitutables datetime
	declare @temp_cool_sugg_date_without_substitutables datetime
	declare @temp_cool_booking_date datetime
	declare @temp_cool_schedule_date datetime
	declare @temp_cool_revised_date datetime
	declare @temp_cool_status varchar(50)
	declare @temp_cool_taxable int
        declare @sales_order_no varchar(50)
	declare @temp_cool_is_cancel int
        declare @final_cool_status varchar(50)
        declare @cslit_status varchar(50)
	declare @simplecount int
	declare @customer_partnumber varchar(50)

	declare @so_booking_date datetime 

	declare @open_orders_cursor CURSOR
	declare @approved_orders_cursor CURSOR
        declare @status_cursor CURSOR

	declare @end_user varchar(50)
	declare @odm_id int
	DECLARE @SHIPPING_TERMS VARCHAR(50)

	declare @result Table(SalesOrder_ID int,
				SO_Customer_PO_Number varchar(50),
				Customer_Id int,
				LineItem_Id int,
				COOL_Line_Item_No int,
				Product_ID int,
				COOL_Description varchar(500),
				COOL_Qty int,
				COOL_Unit_Cost float,
				COOL_Amount float,
				COOL_Requested_Date datetime,
				COOL_Sugg_Schedule_Date_With_Substitutables datetime,
				COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
				COOL_Booking_Date datetime,
				COOL_Schedule_Date datetime,
				COOL_Revised_Date datetime,
				COOL_Status varchar(50),
				COOL_Taxable int,
				COOL_Is_Cancel int,
				SalesOrder_No varchar(50),
				Final_Cool_Status varchar(50),
				Customer_PartNumber varchar(50),
				SO_Booking_Date datetime,
				SO_ODM_Id int,
				SHIPPING_TERMS VARCHAR(50))

	SET @open_orders_cursor = CURSOR FOR 
	    SELECT Sales_Order.SalesOrder_ID ,Sales_Order.SO_Customer_PO_Number,Sales_Order.Customer_Id,LineItem_Id ,COOL_Line_Item_No ,Product_ID ,COOL_Description ,COOL_Qty ,COOL_Unit_Cost ,
	COOL_Amount,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables ,
	COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Booking_Date,COOL_Schedule_Date ,COOL_Revised_Date ,
	COOL_Status ,COOL_Taxable,COOL_Is_Cancel, COOL_CUSTOMER_PARTNUMBER, Sales_Order.SalesOrder_No, Sales_Order.SO_Booking_Date datetime, Sales_Order.SO_EndUser, Sales_Order.SO_ODM_ID, Sales_Order.SHIPPING_TERMS
	FROM Customer_Open_Order_LineItem ,Sales_Order 
	WHERE Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id  and COOL_Status<> 'APPROVED' and Sales_Order.SO_Is_Sample = @isSample
	
	SET @approved_orders_cursor = CURSOR FOR 
	    SELECT Sales_Order.SalesOrder_ID ,Sales_Order.SO_Customer_PO_Number,Sales_Order.Customer_Id,LineItem_Id,COOL_Line_Item_No ,Product_ID ,COOL_Description, COOL_Qty ,COOL_Unit_Cost ,
	COOL_Amount ,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables,
	COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Booking_Date,COOL_Schedule_Date ,COOL_Revised_Date ,
	COOL_Status,COOL_Taxable ,COOL_Is_Cancel, COOL_CUSTOMER_PARTNUMBER, Sales_Order.SalesOrder_No, Sales_Order.SO_Booking_Date datetime, Sales_Order.SO_EndUser, Sales_Order.SO_ODM_ID, Sales_Order.SHIPPING_TERMS
	FROM Customer_Open_Order_LineItem,Sales_Order
	WHERE Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id and COOL_Status = 'APPROVED' and Sales_Order.SO_Is_Sample = @isSample

	open @open_orders_cursor		
		fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
	
	declare @end_user_id int 		
	while @@fetch_status = 0
	begin
		insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
		@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
		@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,@temp_cool_status,@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
		

	  	fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
	
		end 
	close @open_orders_cursor
	deallocate @open_orders_cursor


        open @approved_orders_cursor		
	
	fetch next from @approved_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
	@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
	@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
	@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
			
	while @@fetch_status = 0
	begin

		declare @cslit_status1 varchar(50)
       
		SET @status_cursor = CURSOR FOR 
			SELECT distinct cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id and cslit_status =(select max(cslit_status) from customer_shipping_line_item_table where lineitem_id = @line_item_id )
			declare @tempcount int
			select  @tempcount = count(cs.lineitem_id) from customer_shipping_line_item_table cs , customer_open_order_lineitem c 
				where c.cool_status ='APPROVED' and cs.lineitem_id = @line_item_id and cs.lineitem_id = c.lineitem_id
			if( @tempcount = 0 )
			begin
				insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
			
			end
    
			else
			begin 
				open @status_cursor		
				fetch next from @status_cursor into @cslit_status1
				while @@fetch_status =0
				begin
				
					if( @cslit_status1 = 'ISSUED' )
					begin
						insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id ,  @cool_line_item_no,@temp_product_id, @temp_cool_description, @temp_cool_qty,
						@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
						@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
						@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'ISSUED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
					end
					if( @cslit_status1 = 'CANCELLED' )
					begin
						insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
						@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
						@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
						@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
		        
					end
					if( @cslit_status1 = 'SHIPPED' )
					begin
						insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
						@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
						@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
						@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'PARTIALLY SHIPPED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
						break
					end
					fetch next from @status_cursor into @cslit_status1 
					
	 
				end --status_cursor while
				close @status_cursor
				deallocate @status_cursor
			end --else      
		fetch next from @approved_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS

		end	--approved_orders_cursor while
	
		close @approved_orders_cursor
		deallocate @approved_orders_cursor

		insert @fn_result select * from @result order by SalesOrder_ID,LineItem_Id
return
end



